﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using LumenWorks.Framework.IO.Csv;
using System.IO;
using System.Data.SQLite;

namespace LP_Csharp
{
    public partial class importcsv : UserControl
    {
        public importcsv(Control parent)
        {
            InitializeComponent();
            this.Parent = parent;
            this.Dock = DockStyle.Fill;
            this.Show();

        }

        private void importcsv_Load(object sender, EventArgs e)
        {
            
        }

        /*##############################################################################################################*/
        private void button1_Click(object sender, EventArgs e)
        {
            //condicao de confirmacao da criaçao da base de dados
            if (MessageBox.Show("Deseja mesmo criar a base de dados?\n Este processo pode ser demorado!", "Informação", MessageBoxButtons.YesNo)
            == DialogResult.Yes)
            {
                // definicao dos valores minimo/maximo da progressbar
                this.progressBar1.Value = 0;
                this.progressBar1.Maximum = 12;
                
                //criacao da base de dados
                SQLiteConnection conect = new SQLiteConnection("Data Source = DataBase.db");
                conect.Open();
                SQLiteTransaction transact = conect.BeginTransaction();
                SQLiteCommand cmd = conect.CreateCommand();
                cmd.CommandText = "DROP TABLE IF EXISTS DOCENTES";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS Docentes (cod_docente integer, nome_docente text," +
                                                        "ultimo_grau text,curso text,categoria text," +
                                                        "regime_tempo text,ano_lectivo integer," +
                                                        "tipo_estab text," +
                                                        "nome_estab text)";
                cmd.ExecuteNonQuery();

                // incrementa a progressbar
                this.progressBar1.Value++;

                //carregar os dados dos csv para a base de dados 
                for (int i = 0; i <= 10; i++)
                {
                    CsvReader reader = new CsvReader(new StreamReader("..\\..\\rebcsv\\reb" + i + ".csv"), true);
                    cmd.CommandText = "INSERT INTO DOCENTES (cod_docente,nome_docente,ultimo_grau,curso,categoria,regime_tempo," +
                                                             "ano_lectivo,tipo_estab,nome_estab) VALUES(?,?,?,?,?,?,?,?,?)";
                    int numFields = 9;
                    for (int x = 0; x < numFields; x++)
                    {
                        cmd.Parameters.Add(cmd.CreateParameter());
                    }

                    while (reader.ReadNextRecord())
                    {
                        for (int y = 0; y < numFields; y++)
                        {
                            cmd.Parameters[y].Value = reader[y];
                        }
                        cmd.ExecuteNonQuery();
                    }
                    // incrementa a progressbar
                    this.progressBar1.Value++; 
                }

                //Informacao para que o user saiba que a base de dados esta criada
                MessageBox.Show("A base de dados foi criada.", "Informação");
                this.progressBar1.Value = 0;
                transact.Commit();
                conect.Close();
            }
        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {

        }
    }
}
